CREATE PROCEDURE [dbo].[SlotGet]
@Id						UNIQUEIDENTIFIER,
@ProviderId				UNIQUEIDENTIFIER,
@SpecialtyId			UNIQUEIDENTIFIER,
@ClinicTypeId			UNIQUEIDENTIFIER,
@StartDateTime			DATETIME,
@EndDateTime			DATETIME,
@WeekDays				INT,
@UBRN					INT,
@Status					INT

AS

-- Set Status as NULL to retrieve both Booked and Available Slots
IF @Status = 0
BEGIN
		SET @Status = NULL	
END

SET DATEFIRST 7

SELECT	S.*, CT.Id AS ClinicTypeId, PCT.ProviderId AS ProviderId
FROM	Slot S
INNER JOIN ProviderClinicType PCT ON PCT.Id = S.ProviderClinicTypeId
INNER JOIN ClinicType CT ON PCT.ClinicTypeId = CT.Id
WHERE	
	(@Id IS NULL OR S.Id = @Id)
	AND (@ProviderId IS NULL OR PCT.ProviderId = @ProviderId)
	AND (@ClinicTypeId IS NULL OR PCT.ClinicTypeId = @ClinicTypeId)
	AND (@StartDateTime IS NULL OR S.StartDateTime >= @StartDateTime)
	AND (@EndDateTime IS NULL OR S.EndDateTime <= @EndDateTime)
	AND (@WeekDays IS NULL OR POWER(2,DatePart(dw,S.StartDateTime)) & @WeekDays > 0 )
	AND (@UBRN IS NULL OR S.UBRN = @UBRN)
	AND (@Status IS NULL OR S.Status = @Status)

RETURN @@RowCount
